package cn.zbmao.web.dao;

import cn.zbmao.web.bean.Constants;
import cn.zbmao.web.bean.User;
import cn.zbmao.web.util.DateUtil;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class UserDao {
	/**
	 * �ж��û����Ƿ����
	 * @param name 用户名称
	 * @param pwd 密码
	 * @return
     * 通过用户名称查询用户，当存在该用户，并且密码正确时，返回true，否则返回false
	 */
	public boolean isUser(String name,String pwd){
		User user = User.dao.findFirst("select user_name,user_pwd from "+ Constants.USER+" where user_name = ? ",name);
                                                                    //user
		if (user!=null&&user.getStr("user_pwd").equals(pwd)) {
			return true;
		}
		return false;
	}

    /**
     *
     * @param name 用户名称
     * @return
     * 通过用户名称查询用户，表中有该用户名时返回true，否则返回false
     */
	public boolean isUserSignup(String name){
		User user = User.dao.findFirst("select user_name from "+ Constants.USER+" where user_name = ? ",name);
                                                               //user
		if (user!=null) {
			return true;
		}
		return false;
	}

    /**
     *
     * @param email 用户邮箱
     * @return
     * 同邮箱查询用户，当存在该用户时返回true，否则返回false
     */
	public boolean isUserEmail(String email){
		User user = User.dao.findFirst("select user_email from "+ Constants.USER+" where user_email = ? ", email);
                                                                //user
		if (user!=null) {
			return true;
		}
		return false;
	}

    /**
     *
     * @param email 用户邮箱
     * @param md5 md5处理后的用户邮箱
     * @return
     * 通过用户邮箱查询用户，在修改md5处理后的邮箱
     */
    public Boolean updateUserForEmail(String email,String md5){
        User user = User.dao.findFirst("select * from "+ Constants.USER+" where user_email = ? ", email);
                                                     //user

        if (user!=null) {
            user.set("user_signup_mail_md5",md5).update();
            return true;
        }
        return false;
    }

    /**
     *
     * @param md5 经过md5处理后的邮箱
     * @return
     * 通过md5处理后的邮箱查询出该用户，有则返回true，无这返回false
     */
    public User findMd5(String md5){
        User user = User.dao.findFirst("select id from "+ Constants.USER+" where user_signup_mail_md5 = ? ",md5);
                                                        //user
        if (user!=null) {
            return user;
        }
        return null;
    }

    /**
     *
     * @param user 用户信息
     * @return
     *将该用户信息存入数据库
     */
	public boolean saveUser(User user){
		return user.save();
	}

    /**
     *
     * @param user_id 用户id
     * @param md5 md5处理后的用户邮箱
     * @return
     * 通过用户Id查询一条用户信息，通话 md5处理后的用户邮箱对比，正确返回ture，
     */
	public boolean autoUser(int user_id,String md5){
		User user = User.dao.findById(user_id);
		if (user!=null) {
			return user.getStr("user_signup_mail_md5").equals(md5);
		}else{
			return false;
		}
		
	}

    /**
     *
     * @param user_id
     * @param md5
     * @return
     */
	public boolean autoLoginUser(int user_id,String md5){
		User user = User.dao.findById(user_id);
		if(DateUtil.getDateToInt(user.getTimestamp("user_login_auth_mail_time"))+3600*24< DateUtil.getNowToInt())return false;
		if (user!=null) {
			return user.getStr("user_login_auth_mail_md5").equals(md5);
		}else{
			return false;
		}
		
	}

    /**
     *
     * @param zbmId 登陆cookie
     * @return
     * 通过登陆的cookie查询出该用户
     */
    public User getUserByZbmId(String zbmId){
        return User.dao.findFirst("select * from "+ Constants.USER+" where zbmao_id = ?",zbmId);
    }

    /**
     *
     * @param userId
     * @param cook
     * 更新用户cookie
     */
    public void updateCookie(int userId,String cook){
       Db.update("update user set zbmao_id=? where id=?",cook,userId);
    }

    /**
     * @param user_id
     * 修改用户邮箱为已通过验证邮箱
     */
	public void autoEmail(int user_id){
		User user = User.dao.findById(user_id);
		user.set("user_signup_mail", 1).update();
	}

    /**
     *
     * @param name
     * @return
     * 通过用户名查找用户
     */
	public User getUserByName(String name){
		return User.dao.findFirst("select id,user_name,user_lastlogin_time,user_login_time,user_signup_mail,account_matured_time,account_is_pay,istest from "+ Constants.USER+" where user_name = ?", name);
                                                                                                                                                              //user
	}

    /**
     *
     * @param user_id
     * @return
     * 通过用户Id查询用户
     */
    public User getUserById(int user_id){
        return User.dao.findFirst("select id,user_name,user_email,user_lastlogin_time,user_login_time,user_signup_mail,account_matured_time,account_is_pay,website,phone,istest from "+ Constants.USER+" where id = ?", user_id);
                                                                                                                                                                              //user
    }
    /**
     *
     * @param userId
     * @return
     * 通过用户Id查询用户
     */
	public User getUserByUserId(int userId){
		return User.dao.findFirst("select id,user_name,user_lastlogin_time,user_login_time,user_signup_mail,account_matured_time,account_is_pay,istest from "+ Constants.USER+" where id = ?", userId);
                                                                                                                                                      //user
	}

    /**
     *
     * @param user_id 用户Id
     * @param password 密码
     * @param old_password 旧密码
     * @return
     * 通过用户Id查询用户，对比用户输入的密码是否与表中密码相同，
     * 相同则将表中密码修改为新密码，并返回用户名，不相同则返回null
     */
    public String updatePassword(int user_id,String password,String old_password){
       User user = User.dao.findById(user_id);
        if (user!=null){
            if(old_password.equals(user.getStr("user_pwd"))){
            boolean bl = user.set("user_pwd", password).update();
            if (bl) {
                return user.get("user_name");
            }
            }else{
                return null;
            }
        }
        return null;
    }

    /**
     *
     * @param user_id
     * @param password
     * @return
     * 直接修改密码，成功返回用户名，失败返回空
     */
    public String updatePassword(int user_id,String password){
        User user = User.dao.findById(user_id);
        if (user!=null){

                boolean bl = user.set("user_pwd", password).update();
                if (bl) {
                    return user.get("user_name");
                }

        }
        return null;
    }

    /**
     *
     * @param date 当前登陆时间
     * @param user
     * @return
     * 如果用户当前登陆时间为空，则更新登陆时间为当前时间，
     * 否则修改上一次登陆时间为当前登陆时间，当前登陆时间修改为当前时间
     */
	public boolean updateUserLoginTime(Date date,User user){
		if (user.get("user_login_time")==null) {
			return user.set("user_login_time", date).update();
		}
		return user.set("user_lastlogin_time", user.get("user_login_time")).set("user_login_time", date).update();
	}
	//user_district

    /**
     *
     * @param user_id
     * @return
     * 查询该用户关注的所有地区
     */
//	public List<Record> getUser_district(int user_id){
//		return Db.find("select user_district.id,gd.name,user_district.district_id,user_district.loc_id from " + Constants.USER_DISTRICT + " join " + Constants.GD + " where gd.id = user_district.district_id and user_district.user_id = ?", user_id);
//                                                                                      //user_district                    //gd
//	}
    public List<Record>getUser_district(int user_id){
       return Db.find("select user_district.id,gd.name,user_district.district_id,user_district.loc_id from " + Constants.USER_DISTRICT + " join " + Constants.DISTRICT+ " as gd where gd.code = user_district.district_id and user_district.user_id = ?",user_id);
    }

    public Record getDisByCode(int code){
        return  Db.findFirst("select * from district where code=?",code);
    }

//    public Record isCity(int user_id){
//        return Db.findFirst("select gd.pid from " + Constants.USER_DISTRICT + " join " + Constants.GD + " where  gd.id = user_district.district_id and user_district.user_id = ? and gd.level !=1", user_id);
//
//    }

    /**
     *
     * @param user_id
     * @param id 该地域的上级Id
     * @return
     * 查询某省下的所有城市，并且这些城市时用户没有添加的
     */
//    public List<Record> getUser_district_sel(int user_id,int id){
//        return Db.find("select id,name from "+Constants.GD+" where id not in (select district_id from " + Constants.USER_DISTRICT + " where user_id = ?) and pid = ? ",user_id,id);
//    }
    public List<Record> getUser_district_sel(int user_id,int id){
        return Db.find("select code,name from "+Constants.DISTRICT+" where code not in (select district_id from " + Constants.USER_DISTRICT + " where user_id = ?) and parentid = ? ",user_id,id);
    }

    /**
     *
     * @return
     * 查询所有省份城市
     */
//    public List<Record> getUser_district_def(){
//		return Db.find("select id,name from " + Constants.GD + " where level=1");
//                                           //gd
//	}
    public List<Record> getUser_district_def(){
        return Db.find("select code,name from " + Constants.DISTRICT + " where parentid=0");
        //gd
    }

    /**
     *
     * @param user_id
     * @return
     * 查询层级为1的所有城市，并且这些城市用户没有添加
     */
//    public List<Record> getUser_district_un(int user_id){
//		return Db.find("select  id,name from " + Constants.GD + " where id not in (select district_id from " + Constants.USER_DISTRICT + " where user_id = ?) and level=1", user_id);
//                                                 //gd                                                   //user_district
//	}
    public List<Record> getUser_district_un(int user_id){
        return Db.find("select  id,code,name from " + Constants.DISTRICT + " where code not in (select district_id from " + Constants.USER_DISTRICT + " where user_id = ?) and parentid=0 and suffix!='市' order by initial", user_id);
        //gd                                                   //user_district
    }
    public List<Record> getUser_district_u(int user_id){
        return Db.find("select  id,code,name from " + Constants.DISTRICT + " where code not in (select district_id from " + Constants.USER_DISTRICT + " where user_id = ?) and parentid=0 and suffix='市' order by initial", user_id);
    }

    /**
     *
     * @param id 二级城市Id
     * @return
     * 查询该二级城市所对应的省份
     */
//    public Record getPidById(int id){
//        return Db.findFirst("select g2.name from "+Constants.GD+" g1  JOIN  "+Constants.GD+" g2 on g1.pid=g2.id where g1.id = ?",id);
//    }
    public Record getPidById(int id){
        return Db.findFirst("select g2.name from "+Constants.DISTRICT+" g1  JOIN  "+Constants.DISTRICT+" g2 on g1.parentid=g2.id where g1.code = ?",id);
    }


//    public List<Record> getUser_district_un1(int user_id,int pid){
//        return Db.find("select  id,name from " + Constants.GD + " where id not in (select district_id from " + Constants.USER_DISTRICT + " where user_id = ?) and level !=1 and pid=?", user_id,pid);
//        //gd                                                   //user_district
//    }

    /**
     *
     * @param id
     * @return
     * 通过Id删除用户关联的地域
     */
    public boolean deleteUser_district(int id){
		return Db.deleteById("user_district", id);
	}

    /**
     *
     * @param userId
     * @return
     * 删除该用户关注的搜油地域
     */
    public boolean deleteUser_districtByUserId(int userId){

        return Db.deleteById("user_district","user_id",userId);
    }
//    public List<Record>findByPid(int userId,int pid){
//        String sql="select ud.district_id from user u join user_district ud on u.id=ud.user_id JOIN gd on ud.district_id=gd.id where u.id=? and gd.pid=?";
//        return Db.find(sql,new Object[]{userId,pid});
//    }
    public boolean deleteUser_districtbyPid(int districtId){
//      String sql= "delete from user_district ud where ud.district_id =?";
     return  Db.deleteById("user_district","district_id",districtId);
    }

    /**
     *
     * @param district_id
     * @param id 用户Id
     * @return
     * 通过用户的id与地域Id查询user_districe的Id ,有的话返回true,没有则返回false
     */
	public boolean findUser_district(int district_id,int id){
		List list = Db.find("select id from " + Constants.USER_DISTRICT + " where district_id = ? and user_id = ?", new Object[]{district_id, id});
                                            //user_district
		if (list.isEmpty()) {
			return false;	
		}else{
			return true;
		}
	}

    /**
     *
     * @param district_id
     * @return
     * 查出一条Gd
     */
//    public Record getUser_level(int district_id){
//       return  Db.findById("gd",district_id);
//    }
    public Record getUser_level(int district_id){
        return  Db.findById(Constants.DISTRICT,"code",district_id);
    }

    /**
     *
     * @param district_id 地域Id
     * @param id 用户Id
     * @param level 层级
     * @return
     * 给用户添加一个关注的地域
     */
	public boolean addUser_district(int district_id,int id,int level){
        Record record=null;
        if(level==0){
            record = new Record().set("user_id", id).set("district_id", district_id).set("loc_id",Constants.LOC_ID_PROVINCE);
        }else{
             record = new Record().set("user_id", id).set("district_id", district_id).set("loc_id",Constants.LOC_ID_CITY);
        }

		return Db.save("user_district", record);
	}
	
	//user_keyword

    /**
     *
     * @param user_id
     * @return
     * 查询用户关注所有关键字
     */
	public List<Record> getUser_key(int user_id){
		return Db.find("select id,keyword from " + Constants.USER_KEY_WORD + " where user_id = ?", user_id);
                                                //user_keyword
	}

    /**
     *
     * @param id
     * @return
     * 通过Id删除一条用户关注的关键字
     */
	public boolean deleteUser_keyword(int id){
		return Db.deleteById("user_keyword", id);
	}
	
	//查看是否存在这个关键字   
	//废弃 
	public boolean findUser_keyword(String key,int id){
		List list = Db.find("select id from " + Constants.USER_KEY_WORD + " where keyword = ? and user_id = ?", new Object[]{key, id});
                                               //user_keyword
		if (list.isEmpty()) {
			return false;	
		}else{
			return true;
		}
	}
	//查看用户的关键字列表
	public List<Record> findUser_keyword(int id){
		return Db.find("select keyword from " + Constants.USER_KEY_WORD + " where user_id = ?", id);
                                            //user_keyword
	}
	//查看用户关键字修改次数
	public int findUser_keyword_log(int id,int datetime){
		return Db.find("select id from " + Constants.USER_KEY_WORD_LOG + " where user_id = ? and datetime > ?", new Object[]{id, datetime}).size();
                                          //user_keyword_log
	}

    /**
     *
     * @param key
     * @param id
     * @return
     * 为用户添加一条关注的关键字
     */
	public boolean addUser_keyword(String key,int id){
//		Record record = ;
		return Db.save("user_keyword", new Record().set("user_id", id).set("keyword", key));
	}
	
//	添加日志
	public boolean addUser_keyword_log(String key,int id,int datetime){
//		Record record = ;
		return Db.save("user_keyword_log", new Record().set("user_id", id).set("text", key).set("datetime", datetime).set("operate", 0));
	}
	
	//user_url

    /**
     *
     * @param user_id
     * @return
     * 查询与用户关联的Host信息
     */
	public List<Record> getUser_url(int user_id){
		return Db.find("select  user_url.id,host.url,user_url.host_id from " + Constants.USER_URL + " join " + Constants.HOST + " where user_url.host_id = host.id and user_url.user_id = ?", user_id);
                                                                           //user_url                   //host
	}
	public List<Record> getUser_url_un(int user_id){
		return Db.find("select  id,url from " + Constants.HOST + " where id not in (select host_id from " + Constants.USER_URL + " where user_id = ?)", user_id);
                                            //host                                                     //user_url
	} 
	public boolean deleteUser_url(int id){
		return Db.deleteById("user_url", id);
	}
	public boolean findUser_url(int url_id,int id){
		List list = Db.find("select id from " + Constants.USER_URL + " where host_id = ? and user_id = ?", new Object[]{url_id, id});
                                            //user_url
		if (list.isEmpty()) {
			return false;	
		}else{
			return true;
		}
	}

	public boolean addUser_url(int url_id,int id){
		Record record = new Record().set("user_id", id).set("host_id", url_id);
		return Db.save("user_url", record);
	}
	
	//获取所有付费用户资料
	public List<User> getPayUsers(){
		return User.dao.find("select * from "+ Constants.USER+" where account_is_pay=?", 1);
                                           //user
	}


    /**
     *
     * @param userId
     * @return
     * 查询该用户关注的资质
     */
	public List<Record> findUserQualificationAll(int userId){
		return Db.find("SELECT qualification_id FROM " + Constants.USER_QUALIFICATION + " WHERE user_id = ?", userId);
                                                      //user_qualification
	}

    /**
     *
     * @param userId
     * @return
     * 查询用户关注的资质名称，Id等数据
     */
	public List<Record> findUserQualificationNameAll(int userId){
		return Db.find("SELECT user_qualification.id,user_qualification.qualification_id,qualification.name,qualification.is_ok FROM " + Constants.USER_QUALIFICATION + " join " + Constants.QUALIFICATION + " on user_qualification.qualification_id=qualification.id WHERE user_id = ?", userId);                                                                                                                   //user_qualification             //qualification
	}

    /**
     *
     * @param userId
     * @return
     * 查询出用户关注的资质信息，并且资质信息已经审核通过
     */
    public List<Record> findUserQualificationNameAllWithIsOk(int userId){
        return Db.find("SELECT user_qualification.id,user_qualification.qualification_id,qualification.name,qualification.is_ok FROM " + Constants.USER_QUALIFICATION + " join " + Constants.QUALIFICATION + " on user_qualification.qualification_id=qualification.id WHERE "+Constants.QUALIFICATION+".is_ok = 1 and user_id = ?", userId);                                                                                                                   //user_qualification             //qualification
    }

    /**
     *
     * @param userId 用户Id
     * @return
     */
//    public List<Record> findcipr(int userId){
//      return   Db.find("select gd.level from "+Constants.USER+" as u join "+Constants.USER_DISTRICT+" as ud on u.id=ud.user_id join "+Constants.GD+" as gd on ud.district_id=gd.id where u.id=?",userId);
//    }

    /**
     *
     * return
     * 查询所有用户关注的地域的层级以及Id
     */
    public List<Record> findAllcity(){
        return Db.find("select g.level,u.id from user u JOIN user_district ud on u.id=ud.user_id join gd g on ud.district_id= g.id");
    }

    /**
     *
     * @param userId
     * @return
     * 查询用户关注的资质信息，获取资质信息的名称，以List形式返回
     */
    public List<String> findUserQualificationNameAllStr(int userId){
        List<Record> list = findUserQualificationNameAll(userId);
        List<String> stringList = new ArrayList<String>();
        if (list.size()==0)return stringList;

        for (Record record : list) {
            stringList.add(record.getStr("name"));
        }
        return stringList;
    }

    /**
     *
     * @param userId
     * @return
     * 查询所有资质，但资质中没有用户已经关注的资质
     */
	public List<Record> UnFindUserQualificationNameAll(int userId){
		return Db.find("select id,name,is_ok from " + Constants.QUALIFICATION + " where id not in (select qualification_id from " + Constants.USER_QUALIFICATION + " where user_id = ?) and is_key=1;", userId);
                                               //qualification                                                         //user_qualification
	}

    /**
     *
     * @param userId
     * @return
     * 查询所有资质，但资质中没有用户已经关注的资质,没有通过审核的资质不显示
     */
    public List<Record> UnFindUserQualification(int userId){
        return Db.find("select id,name,is_ok from " + Constants.QUALIFICATION + " where id not in (select qualification_id from " + Constants.USER_QUALIFICATION + " where user_id = ?) and is_key=1 and is_ok!=2 ;", userId);
        //qualification                                                         //user_qualification
    }

    /**
     *
     * @param qualificationId 资质Id
     * @param userId
     * 给用户添加一条关注的资质
     */
	public void addUserQualification(int qualificationId,int userId){
		Db.save("user_qualification", new Record().set("qualification_id", qualificationId).set("user_id", userId));
	}

    /**
     *
     * @param id
     * 通过id删除一条用户关注的资质
     */
	public void deleteUserQualification(int id){
		Db.deleteById("user_qualification", id);
	}

    /**
     * 给用户添加一个搜索模板
     */
    public boolean addModules(int id,String name_d,String keyword_d,Integer search_type_d,Integer tender_type_d,Integer isOverdue_d,String search_subscriber_d
    ,Integer location_d,Integer itemAmount_d,Integer qualification_d){
     return   Db.save("search_modules",new Record().set("user_id",id).set("search_module_name",name_d).set("search_key",keyword_d).set("search_type",search_type_d).set("tender_type",tender_type_d)
         .set("is_overdue",isOverdue_d).set("search_subscriber",search_subscriber_d).set("location_id",location_d).set("item_amount",itemAmount_d).set("qualification_id",qualification_d));
    }

    /**
     * 通过用户名与用户Id查询模板
     * @param userId
     * @param name
     * @return
     */
    public Record findModulesByName(int userId,String name){
        return Db.findFirst("select * from search_modules where user_id=? and search_module_name=?",userId,name);
    }

    /**
     * 通过用户Id查询该用户模板
     * @param userId
     * @return
     */
    public List<Record> findModulesByUserId(int userId){
        return Db.find("select id,search_module_name as name from search_modules where user_id=?",userId);
    }

    /**
     * 通过id查询模板
     * @param id
     * @return
     */
    public Record findModulesById(int id){
        return Db.findFirst("select * from search_modules where id=?",id);
    }

    /**
     * 修改模板
     * @param name_d 模板名称
     * @param keyword_d 关键字
     * @param search_type_d 搜索范围
     * @param tender_type_d 搜索类型
     * @param isOverdue_d 是否过期
     * @param search_subscriber_d 公司名称
     * @param location_d 地域
     * @param itemAmount_d 金额
     * @param qualification_d 资质
     * @param id 模板Id
     * @return
     */
    public int updateModule(String name_d,String keyword_d,Integer search_type_d,Integer tender_type_d,Integer isOverdue_d,String search_subscriber_d
            ,Integer location_d,Integer itemAmount_d,Integer qualification_d,Integer id){
        return Db.update("update search_modules set search_module_name=?,search_key=?,search_type=?,tender_type=?,is_overdue=?,search_subscriber=?,location_id=?,item_amount=?,qualification_id=? " +
                "where id=?",name_d,keyword_d,search_type_d,tender_type_d,isOverdue_d,search_subscriber_d,location_d,itemAmount_d,qualification_d,id);
    }

    /**
     * 通过模板Id删除该模板
     * @param id
     * @return
     */
    public boolean deleteModule(int id){
        return Db.deleteById("search_modules",id);
    }

}
